Request: All SIP PIDs Intersecting Nydia Velazquez’s House Congressional district (D7)

1. Needs all SIP PIDs intersecting disctrict 7

2. Only Planned Projects of 2019 + 2020

3. Databases: DOTDEVRHPGSQL01, SIP

4. Tables: SIP_Projects, SIP_Projects_GEO, Districts_Congressional

In [1]:
from ris import db2  #library designed for SQL database connection and querying
from IPython.display import clear_output
import datetime 
from datetime import date
import pandas as pd
import os


clear_output()
timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
print 'Notebook run: {}'.format(timestamp)
print os.getcwd()
# %load_ext sql 

Notebook run: 2019-08-09 10:17
E:\RIS\Staff Folders\Samuel\Requests\AG\District 7 Sip Projects

Database Connections

In [3]:
#Database connections
#gdb = db2.PostgresDb('dotdevpgsql02', 'GISGRID', quiet = True)
cdb = db2.PostgresDb('DOTDEVRHPGSQL01', 'CRASHDATA', quiet = True)
sdb = db2.PostgresDb('dotpgsql01', 'sip', quiet = True)

User name (CRASHDATA):soge
Password (CRASHDATA)········
User name (sip):soge
Password (sip)········

Function for Writing dataframes to pgsql using sqlalchemy

In [ ]:
from sqlalchemy import create_engine

def df_to_sql(df,tbl_name,db): 
    engine = create_engine('postgresql://{user}:{pw}@10.243.154.88:5432/CRASHDATA'.format(user=db.params['user'],
                                                                                          pw=db.params['password']),
                                                                                          echo=False)
    
    df.to_sql(name='{}'.format(tbl_name), con= engine, if_exists = 'replace', index=False)
    
    db.query("""ALTER TABLE {tbl}
                ALTER COLUMN geom TYPE Geometry USING geom::Geometry;
                
                grant all on {tbl} to public;""".format(tbl=tbl_name))
    
    return tbl_name

All planned sip projects in 2019 and 2020 (Corridors)

In [6]:
d7_corr = (db2.query_to_table(sdb, """                                    
                                    SELECT *
                                    FROM public.sip_projects sp
                                    join public.sip_projects_geo spg
                                    on sp.pid=spg.pid_fk
                                    where sip_year in (2019,2020)
                                    and spg.nodeid=0
                                    and sp.status = '11' """))

All planned sip projects in 2019 and 2020 (Intersections)

In [17]:
d7_itx = (db2.query_to_table(sdb, """                                    
                                    SELECT *
                                    FROM public.sip_projects sp
                                    join public.sip_projects_geo spg
                                    on sp.pid=spg.pid_fk
                                    where sip_year in (2019,2020)
                                    and spg.segmentid=0
                                    and sp.status = '11' """))
In [22]:
#d7_itx
#d7_corr

Writing dataframes to tables in pgsql

In [16]:
d7_pg_corr = df_to_sql(d7_corr,'d7_corr',cdb)
d7_pg_itx = df_to_sql(d7_itx,'d7_itx',cdb)

All Intersection of Sip Projects with district 7

In [ ]:
pjcts = (db2.query_to_table(cdb, """                                    
                                select * from (
                                select distinct pid, pjct_name, sip_year, pm
                                from d7_itx itx
                                join (select * from districts_congressional where congdist = 7) d
                                on st_intersects(st_setsrid(itx.geom,2263), st_setsrid(d.geom,2263))

                                union 

                                select distinct pid, pjct_name, sip_year, pm
                                from d7_corr corr
                                join (select * from districts_congressional where congdist = 7) d
                                on st_intersects(st_setsrid(corr.geom,2263), st_setsrid(d.geom,2263))
                                 ) x

                                """))

Exporting dataframe to csv

In [ ]:
pjcts.to_csv("District 7 Sip Projects-{}.csv".format(datetime.datetime.now().strftime('%Y-%m-%d')),index=False)

Map of Sip Project intersections with District 7

In [2]:
from IPython.display import Image
PATH = os.getcwd()
Image(filename = PATH + "\District 7 Sip Projects.png", width=1000, height=1000)
Out[2]:

Dropping of tempory tables

In [ ]:
cdb.query( """DROP TABLE if exists public.d7_corr;
              DROP TABLE if exists public.d7_itx;""")